--region [dbo].[AddLogTopic]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddLogTopic]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddLogTopic]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddLogTopic]
GO

CREATE PROCEDURE [dbo].[AddLogTopic]
	@Type int,
	@HostUserId int,
	@Title nvarchar(255),
	@Body ntext,
	@PostTime datetime,
	@UpdateTime datetime,
	@ReplyCount int,
	@Hits int,
	@IsTop int
AS

INSERT INTO [LogTopic] (
	[Type],
	[HostUserId],
	[Title],
	[Body],
	[PostTime],
	[UpdateTime],
	[ReplyCount],
	[Hits],
	[IsTop]
) VALUES (
	@Type,
	@HostUserId,
	@Title,
	@Body,
	@PostTime,
	@UpdateTime,
	@ReplyCount,
	@Hits,
	@IsTop
)

GO
--endregion


--region [dbo].[UpdateLogTopic]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateLogTopic]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateLogTopic]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateLogTopic]
GO

CREATE PROCEDURE [dbo].[UpdateLogTopic]
	@Id int,
	@Type int,
	@HostUserId int,
	@Title nvarchar(255),
	@Body ntext,
	@UpdateTime datetime,
	@ReplyCount int,
	@Hits int,
	@IsTop int
AS

UPDATE [LogTopic] SET
	[Type] = @Type,
	[HostUserId] = @HostUserId,
	[Title] = @Title,
	[Body] = @Body,
	[UpdateTime] = @UpdateTime,
	[ReplyCount] = @ReplyCount,
	[Hits] = @Hits,
	[IsTop] = @IsTop
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[UpdateLogTopicHits]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateLogTopicHits]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateLogTopicHits]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateLogTopicHits]
GO

CREATE PROCEDURE [dbo].[UpdateLogTopicHits]
	@Id int
AS

UPDATE [LogTopic] SET
	[Hits] = [Hits] + 1
WHERE
	[Id] = @Id 

GO
--endregion


--region [dbo].[UpdateLogTopicReplyNum]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateLogTopicReplyNum]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateLogTopicReplyNum]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateLogTopicReplyNum]
GO

CREATE PROCEDURE [dbo].[UpdateLogTopicReplyNum]
	@Id int,
	@Num int
AS

UPDATE [LogTopic] SET
	[ReplyCount] = [ReplyCount] + @Num
WHERE
	[Id] = @Id 

GO
--endregion


--region [dbo].[DelLogTopic]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelLogTopic]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelLogTopic]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelLogTopic]
GO

CREATE PROCEDURE [dbo].[DelLogTopic]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[LogTopic]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetLogTopic]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetLogTopic]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetLogTopic]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetLogTopic]
GO

CREATE PROCEDURE [dbo].[GetLogTopic]
	@Id int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[Id],
	[Type],
	[HostUserId],
	[Title],
	[Body],
	[PostTime],
	[UpdateTime],
	[LatestReplyUserId],
	[LatestReplyUserName],
	[LatestReplyTime],
	[ReplyCount],
	[Hits],
	[IsTop]
FROM
	[LogTopic]
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[GetLogTopicList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetLogTopicList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetLogTopicList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetLogTopicList]
GO

CREATE PROCEDURE [dbo].[GetLogTopicList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [LogTopic]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@pagesize) + '
	[Id],
	[Type],
	[HostUserId],
	[Title],
	[Body],
	[PostTime],
	[UpdateTime],
	[LatestReplyUserId],
	[LatestReplyUserName],
	[LatestReplyTime],
	[ReplyCount],
	[Hits],
	[IsTop]
FROM
	[LogTopic]
	'

IF @PageIndex > 1
BEGIN
SET @SQL = @SQL + '
	WHERE [Id] < 
	( 
	SELECT MIN([Id])  
	FROM ( 
	SELECT TOP 
		' + STR((@PageIndex-1)*@PageSize) + ' [Id] 
	FROM [LogTopic] ' 

	IF @WhereCondition <> ''
	BEGIN
		SET @SQL = @SQL + ' WHERE ' + @WhereCondition
	END

	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression + ') AS tblTmp ) '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + ') AS tblTmp ) '
	END

	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression 
	END	
END
ELSE 
BEGIN
	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + ' 
	ORDER BY
		' + @OrderByExpression 
	END	
END

EXEC sp_executesql @SQL

GO
--endregion


